"""
Case Type   : GSC功能模块
Case Name   : gsc打开，非线程池模式，某个库中某张表未进行缓存/已缓场景下，验证该库相应系统缓存信息变化
Create At   : 2023/2/1
Owner       : opentestcase024
Description :
    1、关闭线程池、打开gsc开关
    2、重启数据库，使参数生效;
    3、创建数据库;
    4、创建10个表;
    5、查询所有库并进行连接;
    6、表未缓存时，查看syscache占用情况;
    7、表已缓存时，查看syscache占用情况;
Expect      :
    1、关闭线程池、打开gsc开关; 成功
    2、重启数据库，使参数生效; 重启成功
    3、创建数据库; 创建成功
    4、创建10个表; 创建成功
    5、查询所有库并进行连接; 连接成功
    6、表未缓存时，查看syscache占用情况; 系统缓存增加，包含已缓存的用户表
    7、表已缓存时，查看syscache占用情况; 系统缓存不增加或者增加，增加系统缓存信息不含已缓存的用户表
History     :
    Modified by peilinqian at 2025/1/21:teardown增加集群主备恢复动作，以防对其他用例产生影响
"""
import os
import time
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.com = Common()
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.db_name = 'db_gsc0022'
        self.tb_num = 10
        self.tb_ex_name = 'tb_gsc0022_'
        self.tb_tmp_catalog = 'tb_gsc0022_tmp_catalog'
        self.tb_tmp_table = 'tb_gsc0022_tmp_table'
        self.init_para1 = self.com.show_param("enable_global_syscache")
        self.init_para2 = self.com.show_param('enable_thread_pool')

    def test_main(self):
        if self.init_para1 == "on" and self.init_para2 == "off":
            step_txt = '----线程池、gsc开关设置正常，跳过step1、step2----'
            self.log.info(step_txt)
            self.is_reset_para = False
        else:
            step_txt = '----step1:关闭线程池、打开gsc开关; expect:修改成功----'
            self.log.info(step_txt)
            result = self.sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_global_syscache= on")
            self.assertTrue(result, '执行失败:' + step_txt)
            result = self.sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_thread_pool= off")
            self.assertTrue(result, '执行失败:' + step_txt)

            step_txt = '----step2:重启数据库生效; expect:重启成功----'
            self.log.info(step_txt)
            restart = self.sh.restart_db_cluster()
            self.assertTrue(restart, '执行失败:' + step_txt)
            self.is_reset_para = True

        step_txt = f'----step3:创建数据库; expect:创建成功----'
        self.log.info(step_txt)
        create_db = f'create database {self.db_name};'
        create_result = self.sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS,
                      create_result, '执行失败:' + step_txt)

        step_txt = f'----step4:创建{self.tb_num}个表; expect:创建成功----'
        self.log.info(step_txt)
        for i in range(self.tb_num):
            create_tb_sql = f"create table {self.tb_ex_name}{i} " \
                f"(c_integer integer,c_tinyint tinyint," \
                f"c_smallint smallint,c_binary_integer binary_integer," \
                f"c_bigint bigint,c_numeric numeric(10,4)," \
                f"c_number number(10,4),c_smallserial smallserial)"
            create_result = self.sh.execut_db_sql(create_tb_sql,
                                                  dbname=f'{self.db_name}')
            self.log.info(create_result)
            self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                          '执行失败:' + step_txt)

        step_txt = '----step5:创建中间表，记录gs_gsc_catalog_detail()\
        gs_gsc_table_detail()缓存信息; expect:创建成功----'
        self.log.info(step_txt)
        create_tmp_sql = f"create table {self.tb_tmp_catalog} as " \
            f"select database_id,database_name,rel_id,rel_name," \
            f"cache_id,self,infomask,infomask2,hash_value," \
            f"refcount from gs_gsc_catalog_detail() " \
            f"where database_name = '{self.db_name}';" \
            f"select * from {self.tb_tmp_catalog} " \
            f"where rel_name ='{self.tb_tmp_catalog}';" \
            f"create table {self.tb_tmp_table} as " \
            f"select * from gs_gsc_table_detail() " \
            f"where database_name = '{self.db_name}';" \
            f"select * from {self.tb_tmp_table} " \
            f"where relname ='{self.tb_tmp_table}';"
        tmp_msg = self.sh.execut_db_sql(create_tmp_sql,
                                        dbname=f'{self.db_name}')
        self.log.info(tmp_msg)
        self.assertEqual(2, tmp_msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + step_txt)

        step_txt = '----step6:表未缓存时，查看syscache占用情况; ' \
                   'expect:系统缓存增加，包含已缓存的用户表----'
        self.log.info(step_txt)
        gsc_size2 = 0
        for i in range(self.tb_num):
            self.save_tmp_info()
            check_sql = f"select * from {self.tb_ex_name}{i};" \
                f"select * from gs_gsc_dbstat_info() where " \
                f"database_name = '{self.db_name}';" \
                f"select usedsize from gs_gsc_memory_detail " \
                f"where db_id = (select cast((select oid from " \
                f"pg_database where datname = '{self.db_name}') as text));"
            check_result = self.sh.execut_db_sql(check_sql,
                                                 dbname=f'{self.db_name}')
            self.log.info(check_result)
            gsc_size1 = int(check_result.splitlines()[-2].strip())
            self.assertGreater(gsc_size1, gsc_size2, '执行失败:' + step_txt)
            gsc_size2 = gsc_size1
            recheck_msg = self.recheck_gsc()
            self.assertIn(self.tb_ex_name, recheck_msg, '执行失败:' + step_txt)

        step_txt = '----step7:表已缓存时，查看syscache占用情况; ' \
                   'expect:系统缓存不增加或者增加，增加系统缓存信息不含已缓存的用户表----'
        self.log.info(step_txt)
        for i in range(self.tb_num):
            self.save_tmp_info()
            check_sql = f"select * from {self.tb_ex_name}{i};" \
                f"select * from gs_gsc_dbstat_info() where " \
                f"database_name = '{self.db_name}';" \
                f"select usedsize from gs_gsc_memory_detail " \
                f"where db_id = (select cast((select oid from " \
                f"pg_database where datname = '{self.db_name}') as text));"
            check_result = self.sh.execut_db_sql(check_sql,
                                                 dbname=f'{self.db_name}')
            self.log.info(check_result)
            gsc_size1 = int(check_result.splitlines()[-2].strip())
            if gsc_size1 > gsc_size2:
                recheck_msg = self.recheck_gsc()
                sql = f"select * from gs_gsc_table_detail() " \
                    f"where database_name = '{self.db_name}';"
                msg = self.sh.execut_db_sql(sql,
                                            dbname=f'{self.db_name}')
                self.log.info(msg)
                self.assertNotIn(self.tb_ex_name, recheck_msg,
                                 '执行失败:' + step_txt)
                self.assertLess(recheck_msg.count("(0 rows)"), 2,
                                '执行失败:' + step_txt)
                gsc_size2 = gsc_size1
            self.assertEqual(gsc_size1, gsc_size2, '执行失败:' + step_txt)

    def save_tmp_info(self):
        """
        保存gs_gsc_catalog_detail()以及gs_gsc_table_detail()缓存信息
        :return: 返回插入情况
        """
        tmp_sql = f"delete from {self.tb_tmp_catalog} ;" \
            f"insert into {self.tb_tmp_catalog} " \
            f"select database_id,database_name,rel_id,rel_name," \
            f"cache_id,self,infomask,infomask2,hash_value," \
            f"refcount from gs_gsc_catalog_detail() " \
            f"where database_name = '{self.db_name}';" \
            f"delete from {self.tb_tmp_table};" \
            f"insert into {self.tb_tmp_table} " \
            f"select * from gs_gsc_table_detail() " \
            f"where database_name = '{self.db_name}';"
        tmp_result = self.sh.execut_db_sql(tmp_sql,
                                           dbname=f'{self.db_name}')
        self.log.info(tmp_result)
        return tmp_result

    def recheck_gsc(self):
        """
        比对最新系统缓存信息与历史缓存记录比对，返回新增的系统缓存项
        :return:
        """
        recheck_sql = f"(select database_id,database_name,rel_id," \
            f"rel_name,cache_id,self,infomask,infomask2,hash_value," \
            f"refcount from gs_gsc_catalog_detail() " \
            f"where database_name = '{self.db_name}') except " \
            f"(select * from {self.tb_tmp_catalog});" \
            f"(select * from gs_gsc_table_detail() " \
            f"where database_name = '{self.db_name}') except " \
            f"(select * from {self.tb_tmp_table});"
        recheck_result = self.sh.execut_db_sql(recheck_sql,
                                               dbname=f'{self.db_name}')
        self.log.info(recheck_result)
        return recheck_result

    def tearDown(self):
        step_txt = '----this is teardown----'
        self.log.info(step_txt)
        step1_txt = '----删除数据库 expect:删除成功----'
        self.log.info(step1_txt)
        drop_db = f'drop database {self.db_name}'
        drop_db_result = self.sh.execut_db_sql(drop_db)

        if self.is_reset_para:
            self.step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
            self.log.info(self.step2_txt)
            self.msg1 = self.sh.execute_gsguc('set',
                                              self.constant.GSGUC_SUCCESS_MSG,
                                              f"enable_global_syscache "
                                              f"={self.init_para1}")
            self.msg2 = self.sh.execute_gsguc('set',
                                              self.constant.GSGUC_SUCCESS_MSG,
                                              f"enable_thread_pool "
                                              f"={self.init_para2}")
            self.step3_txt = '----重启数据库生效; expect:重启成功----'
            self.log.info(self.step3_txt)
            self.restart = self.sh.restart_db_cluster()
            self.step4_txt = '----查询数据库状态; expect:状态正常----'
            self.log.info(self.step4_txt)
            status = self.sh.exec_cm_ctl(mode='query', param='-Cv')
            self.log.info(status)
            p_status = status.splitlines()[-1].split('|')[0].split('6001')[-1]
            if 'Primary' in p_status and "cluster_state   : Normal" in status:
                self.log.info('---集群状态正常，无需恢复为初始状态---')
            else:
                recovery = self.sh.exec_cm_ctl(mode='switchover', param='-a')
                self.log.info(recovery)
                time.sleep(10)
            self.status_result = self.sh.get_db_cluster_status('status')

        self.log.info(f'----{os.path.basename(__file__)}:end----')
        flag = self.constant.DROP_DATABASE_SUCCESS
        self.assertIn(flag, drop_db_result, '执行失败:' + step_txt + step1_txt)
        if self.is_reset_para:
            self.assertTrue(self.msg1, '执行失败:' + step_txt + self.step2_txt)
            self.assertTrue(self.msg2, '执行失败:' + step_txt + self.step2_txt)
            self.assertTrue(self.restart,
                            '执行失败:' + step_txt + self.step3_txt)
            self.assertTrue(self.status_result,
                            '执行失败:' + step_txt + self.step4_txt)
